<?php

namespace app\utils\base\database;

class Table_ddl
{
    /**
     * 数据库
     * @var string
     */
    public $create_database='create database %dbname%'; //创建-数据库

    public $show_database='show databases'; //查询-数据库

    public $drop_database='drop database %dbname%'; //删除-数据库

    /**
     * 表
     * @var string
     */
    public $show_field='SHOW FULL COLUMNS FROM %tb_name%'; //查询-表-字段数据

    public $rename_tb_field='ALTER TABLE %tb_name% RENAME COLUMN %old_field% TO %new_field%';//修改-表-字段名称

    public $change_tb_field='ALTER TABLE %tb_name% CHANGE COLUMN %old_field% %new_field%';//修改-表-字段名称(字段类型)

    public $modify_tb_field='ALTER TABLE %tb_name% MODIFY COLUMN %old_field%';//修改-表-字段名称(字段类型)

    public $add_tb_field='alter table %tb_name% add column %field% %pos%';//创建-表-字段

    public $drop_tb_field='alter table %tb_name% drop %field%';//删除-表-字段

    public $create_tb='create table IF NOT EXISTS %tb_name% (%tb_field%) ENGINE = %engine% %collate% %row_format_name% = %row_format_val%'; //创建-表

    public $create_same_tb='CREATE TABLE %new_tb_name% LIKE %old_tb_name%'; //创建-结构相似的表

    public $desc_tb='desc %tb_name%'; //查询-表结构

    public $show_create_tb='show create table %tb_name%'; //查询-表创建结构

    public $drop_tb='DROP TABLE IF EXISTS %tb_name%'; //删除-表

    public $rename_tb='ALTER TABLE %tb_name% RENAME %new_tb_name%'; //修改-表名称

    public $modify_tb_collate='ALTER TABLE %tb_name%  %collate%'; //修改-表字符集

    public $show_tb_status='show table status like %tb_name%'; //查询-表基本信息

    public $set_field_default='ALTER TABLE %tb_name% ALTER COLUMN %field% SET %default%'; //已有字段增加默认值

    public $truncate_tb='truncate table %tb_name%'; //清除表数据

    public $clear_tablespace='OPTIMIZE TABLE %tb_name%'; //清理表空间

    public $show_tb='SHOW TABLES'; //查看-所有表

    /**
     * 字段
     * @var string
     */
    public $tb_field='%field% %field_type% %collate% %null% %default% %extra% %comment%'; //表字段格式

    public $tb_primary_key='PRIMARY KEY (%field%) USING %index_type%'; //主键

    public $tb_index_key='KEY %index_name% (%field%) USING %index_type%'; //索引

    public $tb_foreign_key='CONSTRAINT %foreign_key_name% FOREIGN KEY (%foreign_key_val%) REFERENCES %belong_tb_name% (%belong_tb_field%) %event% %event_op%'; //外键

    /**
     * 索引
     */
    public $add_index='ALTER TABLE %tb_name%  ADD %unique% INDEX %index_name% (%index_field%) USING %index_type%'; //创建-索引

    public $alter_index='ALTER TABLE %tb_name% DROP INDEX %index_name%'; //删除-索引

    public $drop_index='DROP INDEX %index_name% ON %tb_name%'; //删除-索引

    public $show_index='show index from %tb_name%'; //查询-索引

    /**
     * 获取表字段数据
     * @param string $paramObj
     * @return array
     */
    public function getField($paramObj=""){
        $dataObj=paramsObj();
        $dataObj->request=paramsObj();
        $dataObj->data=paramsObj();
        $dataObj->result=array();

        $final_arr=logInit();
        if($final_arr["error_no"]==0){
            $mixed=parseToArr($paramObj,"mixed",0);
            $params=parseToArr($paramObj,"params",array());
            $messages=parseToArr($paramObj,"messages",array());
            $rules=array(
                'tb_name' => 'require',
            );
            $dataDefault=array();
            $paramsObj=paramsObj();
            $paramsObj->params=$params;
            $paramsObj->mixed=$mixed;
            $paramsObj->rules=$rules;
            $paramsObj->messages=$messages;
            $paramsObj->dataDefault=$dataDefault;
            $checkResult = app("verifyParam")::validatorRequest($paramsObj);
            if($checkResult["error_no"]==0){
                $dataObj->request=$checkResult["result"];
            }else{
                $final_arr=logCallErrorMsg($final_arr,$checkResult);
            }
        }
        if($final_arr["error_no"]==0) {
            //检测表是否存在
            $tables=$this->getTables();
            if(!($tables && in_array($dataObj->request->tb_name,$tables))){
                $final_arr = recordLogMsgLevelTwo(app("errorLevel")::E_WARNING, app("errorCode")::OP_FAIL, lang("table_ddl.tableNotExists",["table"=>$dataObj->request->tb_name]));  //表不存在
            }
        }
        if($final_arr["error_no"]==0) {
            $result=$this->getTablesField($dataObj->request->tb_name);
            $fields=array();
            if($result){
                foreach($result as $val){
                    $type=$this->parseType($val["Type"]);
                    $fields[$val["Field"]]=array(
                        "field"=>$this->parseField($val),
                        "meta"=>array(
                            "Field"=>$val["Field"],
                            "Type"=>$type["type"],
                            "Length"=>$type["length"],
                            "Comment"=>$val["Comment"],
                        ),
                    );
                }
            }
            $final_arr["result"]=$fields;
        }
        return $final_arr;
    }

    /**
     * 获取所有表
     */
    public function getTables(){
        $query=Db()->execute($this->show_tb);
        $result=Db()->fetch($query);
        $return=array();
        if($result){
            foreach($result as $val){
                foreach($val as $dbname=>$tbname){
                    $return[]=$tbname;
                }
            }
        }
        return $return;
    }

    /**
     * 获取所有表-字段
     */
    public function getTablesField($tb_name){
        $sql= trim(str_replace(
            ['%tb_name%'],
            [
                $tb_name,
            ],$this->show_field));
        $query=Db()->execute($sql);
        $result=Db()->fetch($query);
        return $result;
    }

    /**
     * 表-添加-字段
     */
    public function addTbField($paramObj=""){
        $dataObj=paramsObj();
        $dataObj->request=paramsObj();
        $dataObj->data=paramsObj();
        $dataObj->result=array();

        $final_arr=logInit();
        if($final_arr["error_no"]==0){
            $mixed=parseToArr($paramObj,"mixed",0);
            $params=parseToArr($paramObj,"params",array());
            $messages=parseToArr($paramObj,"messages",array());
            $rules=array(
                'tb_name' => 'require',
                'field' => 'require',
                'pos' => '',
            );
            $dataDefault=array();
            $dataDefault["pos"]="";
            $paramsObj=paramsObj();
            $paramsObj->params=$params;
            $paramsObj->mixed=$mixed;
            $paramsObj->rules=$rules;
            $paramsObj->messages=$messages;
            $paramsObj->dataDefault=$dataDefault;
            $checkResult = app("verifyParam")::validatorRequest($paramsObj);
            if($checkResult["error_no"]==0){
                $dataObj->request=$checkResult["result"];
            }else{
                $final_arr=logCallErrorMsg($final_arr,$checkResult);
            }
        }
        if($final_arr["error_no"]==0) {
            $field=$this->parseField($dataObj->request->field);
            $sql=$this->parseAddTbField($dataObj->request->tb_name,$field,$dataObj->request->pos);
            $query=Db()->execute($sql);
            if(!$query){
                $final_arr = recordLogMsgLevelTwo(app("errorLevel")::E_WARNING, app("errorCode")::OP_FAIL, lang("table_ddl.tableAddFieldFail",["table"=>$dataObj->request->tb_name,"field"=>$dataObj->request->field["Field"]]));
            }
        }
        return $final_arr;
    }

    /**
     * 表-修改-字段
     */
    public function changeTbField($paramObj=""){
        $dataObj=paramsObj();
        $dataObj->request=paramsObj();
        $dataObj->data=paramsObj();
        $dataObj->result=array();

        $final_arr=logInit();
        if($final_arr["error_no"]==0){
            $mixed=parseToArr($paramObj,"mixed",0);
            $params=parseToArr($paramObj,"params",array());
            $messages=parseToArr($paramObj,"messages",array());
            $rules=array(
                'tb_name' => 'require',
                'field' => 'require',
                'old_field' => 'require',
            );
            $dataDefault=array();
            $paramsObj=paramsObj();
            $paramsObj->params=$params;
            $paramsObj->mixed=$mixed;
            $paramsObj->rules=$rules;
            $paramsObj->messages=$messages;
            $paramsObj->dataDefault=$dataDefault;
            $checkResult = app("verifyParam")::validatorRequest($paramsObj);
            if($checkResult["error_no"]==0){
                $dataObj->request=$checkResult["result"];
            }else{
                $final_arr=logCallErrorMsg($final_arr,$checkResult);
            }
        }
        if($final_arr["error_no"]==0) {
            $field=$this->parseField($dataObj->request->field);
            $sql=$this->parseChangeTbField($dataObj->request->tb_name,$dataObj->request->old_field,$field);
            $query=Db()->execute($sql);
            if(!$query){
                $final_arr = recordLogMsgLevelTwo(app("errorLevel")::E_WARNING, app("errorCode")::OP_FAIL, lang("table_ddl.tableAddFieldFail",["table"=>$dataObj->request->tb_name,"field"=>$dataObj->request->field["Field"]]));
            }
        }
        return $final_arr;
    }

    /**
     * 表-删除-字段
     */
    public function dropTbField($paramObj=""){
        $dataObj=paramsObj();
        $dataObj->request=paramsObj();
        $dataObj->data=paramsObj();
        $dataObj->result=array();

        $final_arr=logInit();
        if($final_arr["error_no"]==0){
            $mixed=parseToArr($paramObj,"mixed",0);
            $params=parseToArr($paramObj,"params",array());
            $messages=parseToArr($paramObj,"messages",array());
            $rules=array(
                'tb_name' => 'require',
                'field' => 'require',
            );
            $dataDefault=array();
            $paramsObj=paramsObj();
            $paramsObj->params=$params;
            $paramsObj->mixed=$mixed;
            $paramsObj->rules=$rules;
            $paramsObj->messages=$messages;
            $paramsObj->dataDefault=$dataDefault;
            $checkResult = app("verifyParam")::validatorRequest($paramsObj);
            if($checkResult["error_no"]==0){
                $dataObj->request=$checkResult["result"];
            }else{
                $final_arr=logCallErrorMsg($final_arr,$checkResult);
            }
        }
        if($final_arr["error_no"]==0) {
            $sql=$this->parseDropTbField($dataObj->request->tb_name,$dataObj->request->field);
            $query=Db()->execute($sql);
            if(!$query){
                $final_arr = recordLogMsgLevelTwo(app("errorLevel")::E_WARNING, app("errorCode")::OP_FAIL, lang("table_ddl.tableAddFieldFail",["table"=>$dataObj->request->tb_name,"field"=>$dataObj->request->field["Field"]]));
            }
        }
        return $final_arr;
    }

    /**
     * 解析-表创建字段
     */
    public function parseAddTbField($tb_name,$field,$pos){
        $sql= trim(str_replace(
            ['%tb_name%','%field%','%pos%'],
            [
                $tb_name,
                $field,
                $pos,
            ],$this->add_tb_field));
        return $sql;
    }

    /**
     * 获取-表字段类型
     */
    public function getTbFieldType($type,$length){
        if(in_array($type,array("float","text"))){
            $type="{$type}";
        }else{
            $type="{$type}({$length})";
        }
        return $type;
    }

    /**
     * 获取-表字段排序规则
     */
    public function getTbFieldCollation($type,$collation){
        $str="";
        if(in_array($type,array("varchar","text"))){
            $str=$collation;
        }
        return $str;
    }

    /**
     * 获取-表默认值
     */
    public function getTbFieldDefault($type,$default_value){
        $default=$default_value;
        if(in_array($type,array("text"))){
            $default="";
        }else if(in_array($type,array("float","int"))){
            if(!$default){
                $default=0;
            }else{
                $default=intval($default);
            }
        }
        return $default;
    }

    /**
     * 解析-表修改字段
     */
    public function parseChangeTbField($tb_name,$old_field,$new_field){
        $sql= trim(str_replace(
            ['%tb_name%','%old_field%','%new_field%'],
            [
                $tb_name,
                $old_field,
                $new_field,
            ],$this->change_tb_field));
        return $sql;
    }

    /**
     * 解析-表删除字段
     */
    public function parseDropTbField($tb_name,$field){
        $sql= trim(str_replace(
            ['%tb_name%','%field%'],
            [
                $tb_name,
                $field,
            ],$this->drop_tb_field));
        return $sql;
    }

    /**
     * 解析-字符集
     */
    public function parseCollation($data){
        $str="";
        if($data){
            list($character,$tmp)=explode("_",$data,2);
            $str.="CHARACTER SET {$character} COLLATE {$data}";
        }
        return $str;
    }

    /**
     * 解析-是否为空
     */
    public function parseNull($data){
        $str="";
        if($data){
            if(strtolower($data)=="no"){
                $str.="NOT NULL";
            }else{
                $str.="NULL";
            }
        }
        return $str;
    }

    /**
     * 解析-默认值
     */
    public function parseDefault($data){
        $str="";
        if(!is_null($data)){
            $str.="DEFAULT '{$data}'";
        }
        return $str;
    }

    /**
     * 解析-类型
     */
    public function parseType($type){
        $return=array();
        if($type){
            //匹配类型和长度
            $reg="/([A-Za-z0-9]+)\((\d+)\)/i";
            $ok=preg_match($reg,$type,$match);
            if($ok){
                $return["type"]=$match[1];
                $return["length"]=$match[2];
            }
        }
        return $return;
    }

    /**
     * 解析-额外数据
     */
    public function parseExtra($data){
        $str="";
        if($data){
            $str.=$data;
        }
        return $str;
    }

    /**
     * 解析-额外注释
     */
    public function parseComment($data){
        $str="";
        if($data){
            $str.="COMMENT '{$data}'";
        }
        return $str;
    }

    /**
     * 解析字段
     */
    public function parseField($data){
        $tb_field=$this->tb_field;
        $sql= trim(str_replace(
            ['%field%','%field_type%','%collate%','%null%','%default%','%extra%','%comment%'],
            [
                getVal($data,"Field",null),
                getVal($data,"Type",null),
                $this->parseCollation(getVal($data,"Collation",null)),
                $this->parseNull(getVal($data,"Null",null)),
                $this->parseDefault(getVal($data,"Default",null)),
                $this->parseExtra(getVal($data,"Extra",null)),
                $this->parseComment(getVal($data,"Comment",null)),
            ],$tb_field));
        return $sql;
    }
}